package h2demo;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
	
	static {
		try {
			Class.forName("org.h2.Driver");
		}catch (ClassNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	private static Connection getConnection() throws SQLException{
		return DriverManager.getConnection("jdbc:h2:~/hedemo");
	}
	
	private static void createTable() {
		try(
				Connection connection = getConnection();
				Statement statement = connection.createStatement()
			){
			String sql = "create table if not exists user("
				+ "id int auto_increment primary key,"
				+ "username varchar(128) unique not null,"
				+ "password varchar(128) not null)";
		statement.execute(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void insertData() {
		try(Connection connection = getConnection();
			Statement statement = connection.createStatement()
			){
			String drop = "delete from user";
			String sql1 ="insert into user(username, password) values('tom', '1234')";
			String sql2 ="insert into user(username, password) values('mike', '2345')";
			statement.execute(drop);
			statement.execute(sql1);
			statement.execute(sql2);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	private static void select() {
		try (Connection connection = getConnection();
			Statement statement = connection.createStatement()
			){
			String sql = "select username,password from user";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				System.out.println("username=" + rs.getString(1) + ", password=" + rs.getString(2));
			}
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		createTable();
		insertData();
		select();
	}
}